# -*- coding: utf-8 -*-
import sys
sys.path.append('../utils/')
sys.path.append('./utils/')

from mysql_connector import Connector

table_sql = """
    INSERT INTO saas_table(tableId, tableName, tenantId)
    VALUES (%d, '%s', %d);
"""
field_sql = """
    INSERT INTO saas_field(fieldId, dataType, fieldName, tableId, fieldNum, tenantId, isIndexed)
    VALUES (%d, '%s', '%s', %d, %d, %d, %d);
"""

# tableId, tableName, tenantId
table_para_list = [
    (1, 'warehouse', 1),
    (2, 'district', 1),
    (3, 'customer', 1),
    (4, 'history', 1),
    (5, 'new_orders', 1),
    (6, 'orders', 1),
    (7, 'order_line', 1),
    (8, 'item', 1),
    (9, 'stock', 1),
]

'''create table warehouse (
w_id smallint not null,
w_name varchar(10), 
w_street_1 varchar(20), 
w_street_2 varchar(20), 
w_city varchar(20), 
w_state char(2), 
w_zip char(9), 
w_tax decimal(4,2), 
w_ytd decimal(12,2),
primary key (w_id) ) Engine=InnoDB;

create table district (
d_id tinyint not null, 
d_w_id smallint not null, 
d_name varchar(10), 
d_street_1 varchar(20), 
d_street_2 varchar(20), 
d_city varchar(20), 
d_state char(2), 
d_zip char(9), 
d_tax decimal(4,2), 
d_ytd decimal(12,2), 
d_next_o_id int,
primary key (d_w_id, d_id) ) Engine=InnoDB;

create table customer (
c_id int not null, 
c_d_id tinyint not null,
c_w_id smallint not null, 
c_first varchar(16), 
c_middle char(2), 
c_last varchar(16), 
c_street_1 varchar(20), 
c_street_2 varchar(20), 
c_city varchar(20), 
c_state char(2), 
c_zip char(9), 
c_phone char(16), 
c_since datetime, 
c_credit char(2), 
c_credit_lim bigint, 
c_discount decimal(4,2), 
c_balance decimal(12,2), 
c_ytd_payment decimal(12,2), 
c_payment_cnt smallint, 
c_delivery_cnt smallint, 
c_data text,
PRIMARY KEY(c_w_id, c_d_id, c_id) ) Engine=InnoDB;

create table history (
h_c_id int, 
h_c_d_id tinyint, 
h_c_w_id smallint,
h_d_id tinyint,
h_w_id smallint,
h_date datetime,
h_amount decimal(6,2), 
h_data varchar(24) ) Engine=InnoDB;

create table new_orders (
no_o_id int not null,
no_d_id tinyint not null,
no_w_id smallint not null,
PRIMARY KEY(no_w_id, no_d_id, no_o_id)) Engine=InnoDB;

create table orders (
o_id int not null, 
o_d_id tinyint not null, 
o_w_id smallint not null,
o_c_id int,
o_entry_d datetime,
o_carrier_id tinyint,
o_ol_cnt tinyint, 
o_all_local tinyint,
PRIMARY KEY(o_w_id, o_d_id, o_id) ) Engine=InnoDB ;

create table order_line ( 
ol_o_id int not null, 
ol_d_id tinyint not null,
ol_w_id smallint not null,
ol_number tinyint not null,
ol_i_id int, 
ol_supply_w_id smallint,
ol_delivery_d datetime, 
ol_quantity tinyint, 
ol_amount decimal(6,2), 
ol_dist_info char(24),
PRIMARY KEY(ol_w_id, ol_d_id, ol_o_id, ol_number) ) Engine=InnoDB ;

create table item (
i_id int not null, 
i_im_id int, 
i_name varchar(24), 
i_price decimal(5,2), 
i_data varchar(50),
PRIMARY KEY(i_id) ) Engine=InnoDB;

create table stock (
s_i_id int not null, 
s_w_id smallint not null, 
s_quantity smallint, 
s_dist_01 char(24), 
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24), 
s_dist_05 char(24), 
s_dist_06 char(24), 
s_dist_07 char(24), 
s_dist_08 char(24), 
s_dist_09 char(24), 
s_dist_10 char(24), 
s_ytd decimal(8,0), 
s_order_cnt smallint, 
s_remote_cnt smallint,
s_data varchar(50),
PRIMARY KEY(s_w_id, s_i_id) ) Engine=InnoDB ;
'''
#fieldId, dataType, fieldName, tableId, fieldNum, tenantId, isIndexed
field_para_list = [
    # warehouse 1
    (1, 'smallint',         'w_id',         1, 1, 1, 0),
    (2, 'varchar(10)',      'w_name',       1, 2, 1, 0),
    (3, 'varchar(20)',      'w_street_1',   1, 3, 1, 0),
    (4, 'varchar(20)',      'w_street_2',   1, 4, 1, 0),
    (5, 'varchar(20)',      'w_city',       1, 5, 1, 0),
    (6, 'char(2)',          'w_state',      1, 6, 1, 0),
    (7, 'char(9)',          'w_zip',        1, 7, 1, 0),
    (8, 'decimal(4,2)',     'w_tax',        1, 8, 1, 0),
    (9, 'decimal(12,2)',    'w_ytd',        1, 9, 1, 0),
    # district 2
    (10, 'tinyint',         'd_id',         2, 1, 1, 0),
    (11, 'smallint',        'd_w_id',       2, 2, 1, 0),
    (12, 'varchar(10)',     'd_name',       2, 3, 1, 0),
    (13, 'varchar(20)',     'd_street_1',   2, 4, 1, 0),
    (14, 'varchar(20)',     'd_street_2',   2, 5, 1, 0),
    (15, 'varchar(20)',     'd_city',       2, 6, 1, 0),
    (16, 'char(2)',         'd_state',      2, 7, 1, 0),
    (17, 'char(9)',         'd_zip',        2, 8, 1, 0),
    (18, 'decimal(4,2)',    'd_tax',        2, 9, 1, 0),
    (19, 'decimal(12,2)',   'd_ytd',        2, 10, 1, 0),
    (20, 'int',             'd_next_o_id',  2, 11, 1, 0),
    # customer 3
    (21, 'int',             'c_id',         3, 1, 1, 0),
    (22, 'tinyint',         'c_d_id',       3, 2, 1, 0),
    (23, 'smallint',        'c_w_id',       3, 3, 1, 0),
    (24, 'varchar(16)',     'c_first',      3, 4, 1, 0),
    (25, 'char(2)',         'c_middle',     3, 5, 1, 0),
    (26, 'varchar(16)',     'c_last',       3, 6, 1, 0),
    (27, 'varchar(20)',     'c_street_1',   3, 7, 1, 0),
    (28, 'varchar(20)',     'c_street_2',   3, 8, 1, 0),
    (29, 'varchar(20)',     'c_city',       3, 9, 1, 0),
    (30, 'char(2)',         'c_state',      3, 10, 1, 0),
    (31, 'char(9)',         'c_zip',        3, 11, 1, 0),
    (32, 'char(16)',        'c_phone',      3, 12, 1, 0),
    (33, 'datetime',        'c_since',      3, 13, 1, 0),
    (34, 'char(2)',         'c_credit',     3, 14, 1, 0),
    (35, 'bigint',          'c_credit_lim', 3, 15, 1, 0),
    (36, 'decimal(4,2)',    'c_discount',   3, 16, 1, 0),
    (37, 'decimal(12,2)',   'c_balance',    3, 17, 1, 0),
    (38, 'decimal(12,2)',   'c_ytd_payment',3, 18 ,1, 0),
    (39, 'smallint',        'c_payment_cnt',3, 19, 1, 0),
    (40, 'smallint',        'c_delivery_cnt',3, 20, 1, 0),
    (41, 'text',            'c_data',       3, 21, 1, 0),
    # history 4
    (42, 'int',             'h_c_id',       4, 1, 1, 0),
    (43, 'tinyint',         'h_c_d_id',     4, 2, 1, 0),
    (44, 'smallint',        'h_c_w_id',     4, 3, 1, 0),
    (45, 'tinyint',         'h_d_id',       4, 4, 1, 0),
    (46, 'smallint',        'h_w_id',       4, 5, 1, 0),
    (47, 'datetime',        'h_date',       4, 6, 1, 0),
    (48, 'decimal(6,2),',   'h_amount',     4, 7, 1, 0),
    (49, 'varchar(24)',     'h_data',       4, 8, 1, 0),
    # new_orders 5
    (50, 'int',             'no_o_id',      5, 1, 1, 0),
    (51, 'tinyint',         'no_d_id',      5, 2, 1, 0),
    (52, 'smallint',        'no_w_id',      5, 3, 1, 0),
    # orders 6
    (53, 'int',             'o_id',         6, 1, 1, 0),
    (54, 'tinyint',         'o_d_id',       6, 2, 1, 0),
    (55, 'smallint',        'o_w_id',       6, 3, 1, 0),
    (56, 'int',             'o_c_id',       6, 4, 1, 0),
    (57, 'datetime',        'o_entry_d',    6, 5, 1, 0),
    (58, 'tinyint',         'o_carrier_id', 6, 6, 1, 0),
    (59, 'tinyint',         'o_ol_cnt',     6, 7, 1, 0),
    (60, 'tinyint',         'o_all_local',  6, 8, 1, 0),
    # order_line 7
    (61, 'int',             'ol_o_id',      7, 1, 1, 0),
    (62, 'tinyint',         'ol_d_id',      7, 2, 1, 0),
    (63, 'smallint',        'ol_w_id',      7, 3, 1, 0),
    (64, 'tinyint',         'ol_number',    7, 4, 1, 0),
    (65, 'int',             'ol_i_id',      7, 5, 1, 0),
    (66, 'smallint',        'ol_supply_w_id',7, 6, 1, 0),
    (67, 'datetime',        'ol_delivery_d',7, 7, 1, 0),
    (68, 'tinyint',         'ol_quantity',  7, 8, 1, 0),
    (69, 'decimal(6,2)',    'ol_amount',    7, 9, 1, 0),
    (70, 'char(24)',        'ol_dist_info', 7, 10, 1, 0),
    # item 8
    (71, 'int',             'i_id',         8, 1, 1, 0),
    (72, 'int',             'i_im_id',      8, 2, 1, 0),
    (73, 'varchar(24)',     'i_name',       8, 3, 1, 0),
    (74, 'decimal(5,2)',    'i_price',      8, 4, 1, 0),
    (75, 'varchar(50)',     'i_data',       8, 5, 1, 0),
    # stock 9
    (76, 'int',             's_i_id',       9, 1, 1, 0),
    (77, 'smallint',        's_w_id',       9, 2, 1, 0),
    (78, 'smallint',        's_quantity',   9, 3, 1, 0),
    (79, 'char(24)',        's_dist_01',    9, 4, 1, 0),
    (80, 'char(24)',        's_dist_02',    9, 5, 1, 0),
    (81, 'char(24)',        's_dist_03',    9, 6, 1, 0),
    (82, 'char(24)',        's_dist_04',    9, 7, 1, 0),
    (83, 'char(24)',        's_dist_05',    9, 8, 1, 0),
    (84, 'char(24)',        's_dist_06',    9, 9, 1, 0),
    (85, 'char(24)',        's_dist_07',    9, 10, 1, 0),
    (86, 'char(24)',        's_dist_08',    9, 11, 1, 0),
    (87, 'char(24)',        's_dist_09',    9, 12, 1, 0),
    (88, 'char(24)',        's_dist_10',    9, 13, 1, 0),
    (89, 'decimal(8,0)',    's_ytd',        9, 14, 1, 0),
    (90, 'smallint',        's_order_cnt',  9, 15, 1, 0),
    (91, 'smallint',        's_remote_cnt', 9, 16, 1, 0),
    (92, 'varchar(50)',     's_data',       9, 17, 1, 0)
]

def translate():
    sql = ""
    for i in table_para_list:
        sql += (table_sql % i)
    for i in field_para_list:
        sql += (field_sql % i)

    conn = Connector()
    conn.connect()
    conn.execute(sql)
    conn.close()

    print('导入表名和属性 tpcc_table --> universal table成功!')
